/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package edu.nii.qlks.BusinessLogicLayer;

import edu.nii.qlks.DataAccessLayer.MSSQLConnectUnit;
import edu.nii.qlks.DataTransferObject.PhieuDenInfo;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Vector;

/**
 *
 * @author MARS_NHO
 */
public class PhieuDenBLL {
    MSSQLConnectUnit _connect;
    ResultSet  _result;
 
    
    public ArrayList LayDS(String Condition, String OderBy) throws Exception{
        ArrayList vt = new ArrayList();
        
        _connect = edu.nii.qlks.BusinessLogicLayer.BusinessLogicAbstract.getConnection();
        _result = _connect.Select("PHIEUDEN", Condition, OderBy);
        
        while (_result.next()){
            ArrayList vt_row = new ArrayList();
            vt_row.add(_result.getString(1));
            vt_row.add(_result.getString(2));
            vt_row.add(_result.getString(3));
            vt_row.add(_result.getString(4));
            vt_row.add(_result.getString(5));
            vt_row.add(_result.getString(6));
            vt_row.add(_result.getString(7));
            vt_row.add(_result.getString(8));
            vt_row.add(_result.getString(9));
            vt.add(vt_row);
        }
        _connect.Close();
        return vt;
    }
    
    public ArrayList LayDS(String Condition) throws Exception{
         return LayDS(Condition, null);
    }
    
    public ArrayList LayDS() throws Exception{
        return LayDS(null);
        
    }
    
    public void Insert(PhieuDenInfo phieuDenInfo) throws Exception{
        _connect = edu.nii.qlks.BusinessLogicLayer.BusinessLogicAbstract.getConnection();
        HashMap<String, Object> map = new HashMap<>();
        map.put("MaPhieuDen", phieuDenInfo.getMaPhieuDen());
        map.put("MaPhong", phieuDenInfo.getMaPhong());
        map.put("MaKhachHang", phieuDenInfo.getMaKhachHang());
        map.put("NgayThuePhong", phieuDenInfo.getNgayThuePhong());
        map.put("NgayTraPhong", phieuDenInfo.getNgayTraPhong());
        map.put("DonGiaThue", phieuDenInfo.getDonGiaThue());
        map.put("SoNgayThue", phieuDenInfo.getSoNgayThue());
        map.put("ThanhTien", phieuDenInfo.getThanhTien());
        map.put("MaPhieuThu", phieuDenInfo.getMaPhieuThu());
        _connect.Insert("PHIEUDEN", map);
    }
    
     
    public void Inserts(HashSet<PhieuDenInfo> phieuThuePhongInfos) throws Exception{
        for (PhieuDenInfo phieuThuePhongInfo:phieuThuePhongInfos) {
            this.Insert(phieuThuePhongInfo);
        }
    }
    
    public void Update(PhieuDenInfo phieuDenInfo) throws Exception{
        _connect = edu.nii.qlks.BusinessLogicLayer.BusinessLogicAbstract.getConnection();
        HashMap<String, Object> map = new HashMap<>();
        map.put("MaPhong", phieuDenInfo.getMaPhong());
        map.put("MaKhachHang", phieuDenInfo.getMaKhachHang());
        map.put("NgayThuePhong", phieuDenInfo.getNgayThuePhong());
        map.put("NgayTraPhong", phieuDenInfo.getNgayTraPhong());
        map.put("DonGiaThue", phieuDenInfo.getDonGiaThue());
        map.put("SoNgayThue", phieuDenInfo.getSoNgayThue());
        map.put("ThanhTien", phieuDenInfo.getThanhTien());
        map.put("MaPhieuThu", phieuDenInfo.getMaPhieuThu());
        _connect.Update("PHIEUDEN", map, "MaPhieuDen = '" + phieuDenInfo.getMaPhieuDen() + "'");
    }
    
    public void Updates(HashSet<PhieuDenInfo> phieuThuePhongInfos) throws Exception{
        for (PhieuDenInfo phieuThuePhongInfo:phieuThuePhongInfos){
            this.Update(phieuThuePhongInfo);
        }
    }
    
    public void Delete(PhieuDenInfo phieuDenInfo) throws Exception{
        _connect = edu.nii.qlks.BusinessLogicLayer.BusinessLogicAbstract.getConnection();
        _connect.Delete("PHIEUDEN", "MaPhieuDen = " + phieuDenInfo.getMaPhieuDen());
    }
    
    public void Deletes(HashSet<PhieuDenInfo> phieuThuePhongInfos) throws Exception{
         for (PhieuDenInfo phieuThuePhongInfo:phieuThuePhongInfos){
            this.Delete(phieuThuePhongInfo);
        }
    }
    
    public String TaoMaTuDong(String maGoc, int batDau){
        return BusinessLogicAbstract.TaoMaTuDong(maGoc, batDau);
    }
    
    
    public Vector LayDS_ChuaTT(String TenPhong) throws Exception{
        Vector vt = new Vector();
        
        _connect = edu.nii.qlks.BusinessLogicLayer.BusinessLogicAbstract.getConnection();
        _result = _connect.Select("MaPhieuThue, PHIEUTHUEPHONG.MaPhong, NgayThuePhong, DonGiaThue, SoNgayThue, ThanhTien, MaHoaDon", 
                "PHIEUTHUEPHONG, PHONG", "PHIEUTHUEPHONG.MaPhong = PHONG.MaPhong " + 
                "and PHIEUTHUEPHONG.SoNgayThue = 0 and PHONG.TenPhong = " 
                + TenPhong + "", null);
        
        while (_result.next()){
            Vector vt_row = new Vector();
            vt_row.add(_result.getString(1));
            vt_row.add(_result.getString(2));
            vt_row.add(_result.getString(3));
            vt_row.add(_result.getString(4));
            vt_row.add(_result.getString(5));
            vt_row.add(_result.getString(6));
            vt_row.add(_result.getString(7));
            vt.add(vt_row);
        }
        _connect.Close();
        return vt;
    }
    
    public Vector LaySoLuongKhach(String MaPhieuThue) throws Exception{
        Vector vt = new Vector();
        
        _connect = edu.nii.qlks.BusinessLogicLayer.BusinessLogicAbstract.getConnection();
        _result = _connect.Select("count(*)", 
                "CHITIETPHIEUTHUE, PHIEUTHUEPHONG", "CHITIETPHIEUTHUE.MaPhieuThue = PHIEUTHUEPHONG.MaPhieuThue " + 
                "and PHIEUTHUEPHONG.SoNgayThue = 0 and PHIEUTHUEPHONG.MaPhieuThue = '" 
                + MaPhieuThue + "'", null);
        
        while (_result.next()){
            Vector vt_row = new Vector();
            vt_row.add(_result.getString(1));
            vt.add(vt_row);
        }
        _connect.Close();
        return vt;
    }
    
    public Vector LayLoaiKhach(String MaPhieuThue) throws Exception{
        Vector vt = new Vector();
        
        _connect = edu.nii.qlks.BusinessLogicLayer.BusinessLogicAbstract.getConnection();
        _result = _connect.Select("LOAIKHACHHANG.MaLoaiKhach, TenLoaiKhach, HeSoLoaiKhach", 
                "LOAIKHACHHANG, KHACHHANG, CHITIETPHIEUTHUE, PHIEUTHUEPHONG", 
                "LOAIKHACHHANG.MaLoaiKhach = KHACHHANG.MaLoaiKhach " + 
                "and KHACHHANG.MaKhachHang = CHITIETPHIEUTHUE.MaKhachHang " + 
                "and CHITIETPHIEUTHUE.MaPhieuThue = PHIEUTHUEPHONG.MaPhieuThue " + 
                "and PHIEUTHUEPHONG.SoNgayThue = 0 and PHIEUTHUEPHONG.MaPhieuThue = '" 
                + MaPhieuThue + "'", null);
        
        while (_result.next()){
            Vector vt_row = new Vector();
            vt_row.add(_result.getString(1));
            vt_row.add(_result.getString(2));
            vt_row.add(_result.getString(3));
            vt.add(vt_row);
        }
        _connect.Close();
        return vt;
    }
    
    public Vector TimKiemNgayThue(String tungay, String denngay) throws Exception{
        Vector vt = new Vector();
        String condition = "";
        condition += " and NgayThuePhong between '"+ tungay + "' and '" + denngay + "'" ;
        _connect = edu.nii.qlks.BusinessLogicLayer.BusinessLogicAbstract.getConnection();
        _result = _connect.TimKiemNgayThue(condition);
        int stt = 0;
        while (_result.next()){
            Vector vt_row = new Vector();
            stt++;
            vt_row.add(stt);
            vt_row.add(_result.getString(1));
            vt_row.add(_result.getString(2));
            vt_row.add(_result.getString(3));
            vt_row.add(_result.getString(4));
            vt.add(vt_row);
        }
        _connect.Close();
        return vt;
    }
    
    public Vector TimKiemNgayTra(String tungay, String denngay) throws Exception{
        Vector vt = new Vector();
        String condition = "";
        condition += " and NgayThanhToan >= '"+ tungay + "' and NgayThanhToan <= '" + denngay + "'" ;
        _connect = edu.nii.qlks.BusinessLogicLayer.BusinessLogicAbstract.getConnection();
        _result = _connect.TimKiemNgayTra(condition);
        int stt = 0;
        while (_result.next()){
            Vector vt_row = new Vector();
            stt++;
            vt_row.add(stt);
            vt_row.add(_result.getString(1));
            vt_row.add(_result.getString(2));
            vt_row.add(_result.getString(3));
            vt_row.add(_result.getString(4));
            vt.add(vt_row);
        }
        _connect.Close();
        return vt;
    }
    
}
